package com.pucong.gitee.database.backup.business.database.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.pucong.gitee.database.backup.business.database.cover.DataSourceBeanConvert;
import com.pucong.gitee.database.backup.business.database.mapper.AutoDataSourceMapper;
import com.pucong.gitee.database.backup.business.database.mapper.AutoExeSqlLogMapper;
import com.pucong.gitee.database.backup.business.database.util.BackupServiceFactory;
import com.pucong.gitee.database.backup.business.database.util.OperationService;
import com.pucong.gitee.database.backup.business.service.mapper.ServiceComputerMapper;
import com.pucong.gitee.database.backup.business.service.mapper.ServiceFileMapper;
import com.pucong.gitee.database.backup.business.sys.mapper.SysUserInfoMapper;
import com.pucong.gitee.database.backup.business.sys.service.SysUserInfoService;
import com.pucong.gitee.database.backup.common.entity.database.AutoDataSource;
import com.pucong.gitee.database.backup.common.entity.database.dto.*;
import com.pucong.gitee.database.backup.common.entity.database.vo.DatabaseTableFiledVo;
import com.pucong.gitee.database.backup.common.entity.database.vo.DatabaseTableVo;
import com.pucong.gitee.database.backup.common.entity.database.vo.DbBackTreeVo;
import com.pucong.gitee.database.backup.common.entity.service.ServiceComputer;
import com.pucong.gitee.database.backup.common.entity.service.ServiceFile;
import com.pucong.gitee.database.backup.common.entity.sys.UserJwt;
import com.pucong.gitee.database.backup.common.selfenum.DatabaseTypeEnum;
import com.pucong.gitee.database.backup.common.selfenum.ServiceFileType;
import com.pucong.gitee.database.backup.common.selfenum.ServiceType;
import com.pucong.gitee.database.backup.common.selfenum.TreeType;
import com.pucong.gitee.database.backup.configuration.exception.BusinessException;
import com.pucong.gitee.database.backup.utils.UUID;
import com.pucong.gitee.database.backup.utils.UserRequest;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.DigestUtils;

import javax.annotation.PostConstruct;
import java.util.*;

import static com.pucong.gitee.database.backup.common.selfenum.DatabaseTypeEnum.*;


/**
 * 数据源信息维护 service实现类
 *
 * @author pucong
 * @url https://gitee.com/pucong
 * @create 2020-06-23
 */
@Service
public class AutoDataSourceServiceImpl implements AutoDataSourceService {

    private Map<DatabaseTypeEnum, TreeType> initTreeTypeMap = new HashMap<>();

    @Autowired
    private AutoDataSourceMapper autoDataSourceMapper;

    @Autowired
    private DatabaseStaticService databaseStaticService;

    @Autowired
    private AutoExeSqlLogMapper autoExeSqlLogMapper;

    @Autowired
    private ServiceComputerMapper serviceComputerMapper;

    @Autowired
    private SysUserInfoMapper sysUserInfoMapper;

    @Autowired
    private SysUserInfoService sysUserInfoService;

    @Autowired
    private ServiceFileMapper serviceFileMapper;

    @Autowired
    private DataSourceBeanConvert dataSourceBeanConvert;

    @Autowired
    private BackupServiceFactory backupServiceFactory;

    @Override
    public IPage<AutoDataSource> getDatabaseListByPage(int pageNum, int pageSize, AutoDataSourceSelectDto dto) {
        Page<AutoDataSource> page = new Page<>(pageNum, pageSize);
        List<String> userIds = sysUserInfoService.selectChildrenUserId();
        if (userIds.size() == 0) {
            return page;
        }
        //构造分页查询
        LambdaQueryWrapper<AutoDataSource> params = new LambdaQueryWrapper<>();
        params.eq(StringUtils.isNotBlank(dto.getConnectName()), AutoDataSource::getConnectName, dto.getConnectName())
                .eq(StringUtils.isNotBlank(dto.getDatabaseName()), AutoDataSource::getDatabaseName, dto.getDatabaseName())
                .in(userIds != null, AutoDataSource::getCreateBy, userIds);

        params.orderByDesc(AutoDataSource::getCreateTime);
        IPage<AutoDataSource> backupDatabaseIPage = autoDataSourceMapper.selectPage(page, params);
        return backupDatabaseIPage;
    }

    @Override
    public AutoDataSource getBaseInfoById(String id) {
        AutoDataSource autoDataSource = autoDataSourceMapper.selectById(id);
        autoDataSource.setPassword("");
        return autoDataSource;
    }

    @Override
    public int saveBaseInfo(AutoDataSourceSaveDto dto) {
        AutoDataSource autoDataSource = dataSourceBeanConvert.dto2do(dto);
        if (StringUtils.isBlank(autoDataSource.getDatabaseName())) {
            if (autoDataSource.getDatabaseType() == DatabaseTypeEnum.postgresql) {
                throw new BusinessException("数据库名称必填");
            }
            if (autoDataSource.getDatabaseType() == DatabaseTypeEnum.oracle) {
                throw new BusinessException("SID名称必填");
            }
        }

        if (StringUtils.isBlank(dto.getUuid())) {
            UserJwt loginUser = UserRequest.getLoginUser();
            autoDataSource.setUuid(UUID.uuid());
            autoDataSource.setCreateTime(new Date());
            autoDataSource.setCreateBy(loginUser.getUuid());
            autoDataSourceMapper.insert(autoDataSource);
        } else {
            autoDataSource.setCreateTime(null);
            autoDataSource.setCreateBy(null);
            autoDataSourceMapper.updateById(autoDataSource);
        }
        return 1;
    }

    @Override
    public Boolean connectTest(AutoDataSourceTestConnectDto dto) {
        AutoDataSource autoDataSource = dataSourceBeanConvert.dto2do(dto);
        OperationService operationService = backupServiceFactory.getBackupService(autoDataSource, autoExeSqlLogMapper);
        Boolean connection = operationService.getConnection();
        operationService.closeAll();
        return connection;
    }

    @Override
    public List<DbBackTreeVo> getTreeList(SelectDbBackTreeDto dto) {
        List<DbBackTreeVo> dbBackTreeVos = new ArrayList<>();
        if (StringUtils.isNotBlank(dto.getUuid())) {
            switch (dto.getTreeType()) {
                case 数据源:
                    // 获取数据源下的数据库
                    return getDataBaseTreeList(dto);
                case 数据库:
                    // 获取数据库下的模式
                    return getDBSchemaTreeList(dto);
                case 模式:
                    // 获取明细列表
                    return getDetailTreeList(dto);
                case 表:
                    // 获取表
                    return getDBTableTreeList(dto);
                case 表sub:
                    // 获取表下的列
                    return getDBFiledTreeList(dto);
                case 查询:
                    // 获取查询
                    return getDBFiledTreeList(dto);
            }
            return dbBackTreeVos;
        } else {
            /**
             * 查询一级列表
             * 权限查询
             */
            List<String> ids = sysUserInfoService.selectChildrenUserId();
            // 数据源
            LambdaQueryWrapper<AutoDataSource> backupDatabaseParams = new LambdaQueryWrapper<>();
            backupDatabaseParams.in(ids != null, AutoDataSource::getCreateBy, ids);
            List<AutoDataSource> autoDataSources = autoDataSourceMapper.selectList(backupDatabaseParams);
            // 服务器
            LambdaQueryWrapper<ServiceComputer> serviceComputerParams = new LambdaQueryWrapper<>();
            serviceComputerParams.in(ids != null, ServiceComputer::getCreateBy, ids);
            List<ServiceComputer> serviceComputers = serviceComputerMapper.selectList(serviceComputerParams);
            // 文件服务器
            LambdaQueryWrapper<ServiceFile> serviceFileParams = new LambdaQueryWrapper<>();
            serviceFileParams.in(ids != null, ServiceFile::getCreateBy, ids);
            List<ServiceFile> serviceFiles = serviceFileMapper.selectList(serviceFileParams);
            for (AutoDataSource autoDataSource : autoDataSources) {
                DbBackTreeVo dbBackTreeVo = new DbBackTreeVo();
                dbBackTreeVo.setId(autoDataSource.getUuid());
                dbBackTreeVo.setDataSourceId(autoDataSource.getUuid());
                dbBackTreeVo.setPId("0");
                dbBackTreeVo.setTreeName(autoDataSource.getConnectName());
                dbBackTreeVo.setTreeType(TreeType.数据源);
                dbBackTreeVo.setDatabaseType(autoDataSource.getDatabaseType());
                dbBackTreeVos.add(dbBackTreeVo);
            }
            for (ServiceComputer serviceComputer : serviceComputers) {
                DbBackTreeVo dbBackTreeVo = new DbBackTreeVo();
                dbBackTreeVo.setId(serviceComputer.getUuid());
                dbBackTreeVo.setPId("0");
                dbBackTreeVo.setTreeName(serviceComputer.getHost());
                if (ServiceType.linux == serviceComputer.getServiceType()) {
                    dbBackTreeVo.setTreeType(TreeType.linux服务器);
                } else {
                    dbBackTreeVo.setTreeType(TreeType.Windows服务器);
                }
                dbBackTreeVo.setLeaf(true);
                dbBackTreeVos.add(dbBackTreeVo);
            }
            for (ServiceFile serviceFile : serviceFiles) {
                DbBackTreeVo dbBackTreeVo = new DbBackTreeVo();
                dbBackTreeVo.setId(serviceFile.getUuid());
                dbBackTreeVo.setPId("0");
                dbBackTreeVo.setTreeName(serviceFile.getServiceFileName());
                if (ServiceFileType.FTP == serviceFile.getServiceFileType()) {
                    dbBackTreeVo.setTreeType(TreeType.ftp服务);
                } else if(ServiceFileType.SFTP == serviceFile.getServiceFileType()){
                    dbBackTreeVo.setTreeType(TreeType.sftp服务);
                }
                dbBackTreeVo.setLeaf(true);
                dbBackTreeVos.add(dbBackTreeVo);
            }
            return dbBackTreeVos;
        }
    }

    private List<DbBackTreeVo> getDetailTreeList(SelectDbBackTreeDto dto) {
        AutoDataSource db = autoDataSourceMapper.selectById(dto.getDataSourceId());

        List<DbBackTreeVo> dbBackTreeVos = new ArrayList<>();
        DbBackTreeVo dbBackTreeVo = new DbBackTreeVo();
        dbBackTreeVo.setId(dto.getUuid() + "table");
        dbBackTreeVo.setDataSourceId(dto.getDataSourceId());
        dbBackTreeVo.setPId(dto.getUuid());
        dbBackTreeVo.setTreeName("表");
        dbBackTreeVo.setSchemaName(dto.getSchemaName());
        dbBackTreeVo.setDatabaseName(dto.getDatabaseName());
        dbBackTreeVo.setTreeType(TreeType.表);
        dbBackTreeVo.setDatabaseType(db.getDatabaseType());
        dbBackTreeVos.add(dbBackTreeVo);

        DbBackTreeVo dbBackTreeVo6 = new DbBackTreeVo();
        dbBackTreeVo6.setId(dto.getUuid() + "select");
        dbBackTreeVo6.setDataSourceId(dto.getDataSourceId());
        dbBackTreeVo6.setPId(dto.getUuid());
        dbBackTreeVo6.setTreeName("查询");
        dbBackTreeVo6.setSchemaName(dto.getSchemaName());
        dbBackTreeVo6.setDatabaseName(dto.getDatabaseName());
        dbBackTreeVo6.setTreeType(TreeType.查询);
        dbBackTreeVo6.setDatabaseType(db.getDatabaseType());
        dbBackTreeVos.add(dbBackTreeVo6);


        DbBackTreeVo dbBackTreeVo2 = new DbBackTreeVo();
        dbBackTreeVo2.setId(dto.getUuid() + "views");
        dbBackTreeVo2.setDataSourceId(dto.getDataSourceId());
        dbBackTreeVo2.setPId(dto.getUuid());
        dbBackTreeVo2.setTreeName("视图");
        dbBackTreeVo2.setSchemaName(dto.getSchemaName());
        dbBackTreeVo2.setDatabaseName(dto.getDatabaseName());
        dbBackTreeVo2.setTreeType(TreeType.视图);
        dbBackTreeVo2.setDatabaseType(db.getDatabaseType());
        dbBackTreeVos.add(dbBackTreeVo2);

        DbBackTreeVo dbBackTreeVo3 = new DbBackTreeVo();
        dbBackTreeVo3.setId(dto.getUuid() + "back");
        dbBackTreeVo3.setDataSourceId(dto.getDataSourceId());
        dbBackTreeVo3.setPId(dto.getUuid());
        dbBackTreeVo3.setTreeName("备份");
        dbBackTreeVo3.setSchemaName(dto.getSchemaName());
        dbBackTreeVo3.setDatabaseName(dto.getDatabaseName());
        dbBackTreeVo3.setTreeType(TreeType.备份);
        dbBackTreeVo3.setDatabaseType(db.getDatabaseType());
        dbBackTreeVos.add(dbBackTreeVo3);

        DbBackTreeVo dbBackTreeVo4 = new DbBackTreeVo();
        dbBackTreeVo4.setId(dto.getUuid() + "role");
        dbBackTreeVo4.setDataSourceId(dto.getDataSourceId());
        dbBackTreeVo4.setPId(dto.getUuid());
        dbBackTreeVo4.setTreeName("角色");
        dbBackTreeVo4.setSchemaName(dto.getSchemaName());
        dbBackTreeVo4.setDatabaseName(dto.getDatabaseName());
        dbBackTreeVo4.setTreeType(TreeType.角色);
        dbBackTreeVo4.setDatabaseType(db.getDatabaseType());
        dbBackTreeVos.add(dbBackTreeVo4);

        DbBackTreeVo dbBackTreeVo5 = new DbBackTreeVo();
        dbBackTreeVo5.setId(dto.getUuid() + "function");
        dbBackTreeVo5.setDataSourceId(dto.getDataSourceId());
        dbBackTreeVo5.setPId(dto.getUuid());
        dbBackTreeVo5.setTreeName("函数");
        dbBackTreeVo5.setSchemaName(dto.getSchemaName());
        dbBackTreeVo5.setDatabaseName(dto.getDatabaseName());
        dbBackTreeVo5.setTreeType(TreeType.函数);
        dbBackTreeVo5.setDatabaseType(db.getDatabaseType());
        dbBackTreeVos.add(dbBackTreeVo5);

        return dbBackTreeVos;
    }

    @Override
    public void deleteTree(SelectDbBackTreeDto dto) {
        switch (dto.getTreeType()) {
            case 数据源:
                autoDataSourceMapper.deleteById(dto.getUuid());
                break;
            case 数据库:

            case 模式:

            case 表sub:

            case 查询sub:

            case 角色sub:

            case 备份sub:

            case 视图sub:

            case linux服务器:
                serviceComputerMapper.deleteById(dto.getUuid());
                break;
            case Windows服务器:
                serviceComputerMapper.deleteById(dto.getUuid());
                break;
            case ftp服务:
                serviceFileMapper.deleteById(dto.getUuid());
                break;
            case sftp服务:
                serviceFileMapper.deleteById(dto.getUuid());
                break;
        }
    }

    @Override
    public Boolean rename(RenameDataSourceDto dto) {
        switch (dto.getTreeType()) {
            case 数据源:

            case 数据库:

            case 模式:

            case 表:

            case 表sub:

            case 查询:

        }
        return true;
    }

    /**
     * 查询字段的tree list
     *
     * @param dto
     * @return
     */
    private List<DbBackTreeVo> getDBFiledTreeList(SelectDbBackTreeDto dto) {
        List<DbBackTreeVo> dbBackTreeVos = new ArrayList<>();

        AutoDataSource db = autoDataSourceMapper.selectById(dto.getDataSourceId());
        db.setDatabaseName(dto.getDatabaseName());
        db.setSchemaName(dto.getSchemaName());
        OperationService backupService = backupServiceFactory.getBackupService(db, autoExeSqlLogMapper);
        List<DatabaseTableFiledVo> tableFiledListByDBInfo = backupService.getTableFiledListByDBInfo(dto.getDatabaseName(), dto.getSchemaName(), dto.getTableName());
        backupService.closeAll();
        for (DatabaseTableFiledVo databaseTableFiledVo : tableFiledListByDBInfo) {
            DbBackTreeVo dbBackTreeVo = new DbBackTreeVo();
            dbBackTreeVo.setId(dto.getUuid() + databaseTableFiledVo.getFiledName().hashCode() + "");
            dbBackTreeVo.setPId(dto.getUuid());
            dbBackTreeVo.setDataSourceId(dto.getDataSourceId());
            dbBackTreeVo.setSchemaName(dto.getSchemaName());
            dbBackTreeVo.setDatabaseName(dto.getDatabaseName());
            dbBackTreeVo.setTableName(dto.getTableName());
            dbBackTreeVo.setFiledName(databaseTableFiledVo.getFiledName());
            dbBackTreeVo.setTreeName(databaseTableFiledVo.getFiledName());
            dbBackTreeVo.setTreeType(TreeType.字段);
            dbBackTreeVo.setDatabaseType(db.getDatabaseType());
            dbBackTreeVo.setLeaf(true);
            dbBackTreeVos.add(dbBackTreeVo);
        }
        return dbBackTreeVos;
    }

    /**
     * 查询模式的tree list
     *
     * @param dto
     * @return
     */
    private List<DbBackTreeVo> getDBTableTreeList(SelectDbBackTreeDto dto) {
        List<DbBackTreeVo> dbBackTreeVos = new ArrayList<>();

        AutoDataSource db = autoDataSourceMapper.selectById(dto.getDataSourceId());
        db.setDatabaseName(dto.getDatabaseName());
        db.setSchemaName(dto.getSchemaName());
        // 查询数据源下的表
        OperationService backupService = backupServiceFactory.getBackupService(db, autoExeSqlLogMapper);
        List<DatabaseTableVo> tableListByDBInfo = backupService.getTableListByDBInfo(dto.getDatabaseName(), dto.getSchemaName());
        backupService.closeAll();
        for (DatabaseTableVo databaseTableVo : tableListByDBInfo) {
            DbBackTreeVo dbBackTreeVo = new DbBackTreeVo();
            dbBackTreeVo.setId(databaseTableVo.getUuid());
            dbBackTreeVo.setPId(dto.getUuid());
            dbBackTreeVo.setDataSourceId(dto.getDataSourceId());
            dbBackTreeVo.setSchemaName(dto.getSchemaName());
            dbBackTreeVo.setDatabaseName(dto.getDatabaseName());
            dbBackTreeVo.setTableName(databaseTableVo.getTableName());
            dbBackTreeVo.setTreeName(databaseTableVo.getTableName());
            dbBackTreeVo.setTreeType(TreeType.表sub);
            dbBackTreeVo.setDatabaseType(db.getDatabaseType());
            dbBackTreeVo.setLeaf(false);
            dbBackTreeVos.add(dbBackTreeVo);
        }
        return dbBackTreeVos;
    }

    /**
     * 查询模式的tree list
     *
     * @param dto
     * @return
     */
    private List<DbBackTreeVo> getDBSchemaTreeList(SelectDbBackTreeDto dto) {
        List<DbBackTreeVo> dbBackTreeVos = new ArrayList<>();

        AutoDataSource db = autoDataSourceMapper.selectById(dto.getDataSourceId());
        // 如果是mysql则直接查询明细
        if(db.getDatabaseType() == mysql){
            return getDetailTreeList(dto);
        }
        db.setDatabaseName(dto.getDatabaseName());
        // 查询数据源下的模式
        OperationService backupService = backupServiceFactory.getBackupService(db, autoExeSqlLogMapper);
        List<String> dataBaseList = backupService.getSchemaList(dto.getDatabaseName());
        backupService.closeAll();
        for (String name : dataBaseList) {
            DbBackTreeVo dbBackTreeVo = new DbBackTreeVo();
            dbBackTreeVo.setId(dto.getUuid() + name.hashCode());
            dbBackTreeVo.setPId(dto.getUuid());
            dbBackTreeVo.setDataSourceId(dto.getDataSourceId());
            dbBackTreeVo.setDatabaseName(dto.getDatabaseName());
            dbBackTreeVo.setSchemaName(name);
            dbBackTreeVo.setTreeName(name);
            dbBackTreeVo.setTreeType(TreeType.模式);
            dbBackTreeVo.setDatabaseType(db.getDatabaseType());
            dbBackTreeVo.setLeaf(false);
            dbBackTreeVos.add(dbBackTreeVo);
        }
        return dbBackTreeVos;
    }

    /**
     * 查询数据源tree list
     *
     * @param dto
     * @return
     */
    private List<DbBackTreeVo> getDataBaseTreeList(SelectDbBackTreeDto dto) {
        List<DbBackTreeVo> dbBackTreeVos = new ArrayList<>();

        AutoDataSource db = autoDataSourceMapper.selectById(dto.getDataSourceId());
        // 查询数据源下的数据库
        OperationService backupService = backupServiceFactory.getBackupService(db, autoExeSqlLogMapper);
        List<String> dataBaseList = backupService.getDataBaseList();
        backupService.closeAll();
        for (String name : dataBaseList) {
            DbBackTreeVo dbBackTreeVo = new DbBackTreeVo();
            dbBackTreeVo.setId(dto.getDataSourceId() + name.hashCode());
            dbBackTreeVo.setPId(dto.getUuid());
            dbBackTreeVo.setDataSourceId(dto.getDataSourceId());
            dbBackTreeVo.setDatabaseName(name);
            dbBackTreeVo.setTreeName(name);
            dbBackTreeVo.setTreeType(initTreeTypeMap.get(db.getDatabaseType()));
            dbBackTreeVo.setDatabaseType(db.getDatabaseType());
            dbBackTreeVo.setLeaf(false);
            dbBackTreeVos.add(dbBackTreeVo);
        }
        return dbBackTreeVos;
    }

    @PostConstruct
    private void initTreeTypeMap() {
        initTreeTypeMap.put(mysql, TreeType.模式);
        initTreeTypeMap.put(oracle, TreeType.数据库);
        initTreeTypeMap.put(postgresql, TreeType.数据库);
        initTreeTypeMap.put(sqlserver, TreeType.数据库);
        initTreeTypeMap.put(hive, TreeType.数据库);
    }
}
